--    Program: 		CAPPOEXT_APPROVED_SUPPLIER_LIST.sql
--    Description:	Extract PO Approved Supplier List information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
  l_count				number;
  l_processed_count_1 	number;
  l_rejected_count_1  	number;
  l_processed_count_2	number;
  l_rejected_count_2	number;
  l_err_msg       		varchar2(2000);
  
  l_utl_file_1  	utl_file.file_type;
  l_utl_file_2 		utl_file.file_type;

  l_filename_1 		varchar2(255);
  l_filename_2 		varchar2(255);
  l_pathname  		varchar2(100);

  l_ngo_sob			varchar2(20);
  
  l_set_of_books_id number;
  
  cursor cur_1 (p_set_of_books_id number) is
	select pasl.*
	from   po_approved_supplier_list pasl, 
	       hr_organization_information hoi
	where  pasl.using_organization_id = hoi.organization_id and 
	       hoi.org_information_context = 'Accounting Information' and
 	       hoi.org_information1 = to_char(p_set_of_books_id);
	
  cursor cur_2 (p_using_organization_id number, p_asl_id number) is
	select * 
	from po_asl_attributes
    where using_organization_id = p_using_organization_id and
	      asl_id = p_asl_id;


begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPPO_APPROVED_SUPPLIER_LIST.dat';
  l_filename_2 := 'CAPPO_APPROVED_SUPPLIER_LIST_ATTRIBUTE.dat';
  
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w', 32767);
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w', 32767);
  
  l_count := 0;
  l_processed_count_1 := 0;
  l_rejected_count_1  := 0;
  l_processed_count_2 := 0;
  l_rejected_count_2  := 0;
                                         
  l_ngo_sob := '&1';
  
     begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob ||': set of books name not found');
       --raise;
   end;    
  
    for cur1 in cur_1(l_set_of_books_id) loop   	
     
     -- output data
     utl_file.put_line( l_utl_file_1, 
	  cur1.ASL_ID || chr(9) ||
	  cur1.USING_ORGANIZATION_ID || chr(9) ||
	  cur1.OWNING_ORGANIZATION_ID || chr(9) ||
	  cur1.VENDOR_BUSINESS_TYPE || chr(9) ||
	  cur1.ASL_STATUS_ID || chr(9) ||
	  cur1.LAST_UPDATE_DATE || chr(9) ||
	  cur1.LAST_UPDATED_BY || chr(9) ||
	  cur1.CREATION_DATE || chr(9) ||
	  cur1.CREATED_BY || chr(9) ||
	  cur1.MANUFACTURER_ID || chr(9) ||
	  cur1.VENDOR_ID || chr(9) ||
	  cur1.ITEM_ID || chr(9) ||
	  cur1.CATEGORY_ID || chr(9) ||
	  cur1.VENDOR_SITE_ID || chr(9) ||
	  cur1.PRIMARY_VENDOR_ITEM || chr(9) ||
	  cur1.MANUFACTURER_ASL_ID || chr(9) ||
	  cur1.REVIEW_BY_DATE || chr(9) ||
	  cur1.COMMENTS || chr(9) ||              
	  cur1.ATTRIBUTE_CATEGORY || chr(9) ||          
	  cur1.ATTRIBUTE1 || chr(9) ||          
	  cur1.ATTRIBUTE2 || chr(9) ||                              
	  cur1.ATTRIBUTE3 || chr(9) ||                  
	  cur1.ATTRIBUTE4 || chr(9) ||                  
	  cur1.ATTRIBUTE5 || chr(9) ||                  
	  cur1.ATTRIBUTE6 || chr(9) ||                  
	  cur1.ATTRIBUTE7 || chr(9) ||                  
	  cur1.ATTRIBUTE8 || chr(9) ||                  
	  cur1.ATTRIBUTE9 || chr(9) ||                  
	  cur1.ATTRIBUTE10 || chr(9) ||                  
	  cur1.ATTRIBUTE11 || chr(9) ||                 
	  cur1.ATTRIBUTE12 || chr(9) ||                 
	  cur1.ATTRIBUTE13 || chr(9) ||                 
	  cur1.ATTRIBUTE14 || chr(9) ||                 
	  cur1.ATTRIBUTE15 || chr(9) ||                 
	  cur1.LAST_UPDATE_LOGIN || chr(9) ||
	  cur1.REQUEST_ID || chr(9) ||
	  cur1.PROGRAM_APPLICATION_ID || chr(9) ||
	  cur1.PROGRAM_ID || chr(9) ||
	  cur1.PROGRAM_UPDATE_DATE || chr(9) ||
	  cur1.DISABLE_FLAG
	 );
     
	 for cur2 in cur_2(cur1.USING_ORGANIZATION_ID, cur1.ASL_ID) loop
	 	  utl_file.put_line( l_utl_file_2,
		  cur2.ASL_ID                          || chr(9) ||
		  cur2.USING_ORGANIZATION_ID           || chr(9) || 
		  cur2.LAST_UPDATE_DATE                || chr(9) ||
		  cur2.LAST_UPDATED_BY                 || chr(9) ||
		  cur2.CREATION_DATE                   || chr(9) ||
          cur2.CREATED_BY                      || chr(9) ||
          cur2.DOCUMENT_SOURCING_METHOD        || chr(9) ||
          cur2.RELEASE_GENERATION_METHOD       || chr(9) ||
          cur2.PURCHASING_UNIT_OF_MEASURE      || chr(9) ||
          cur2.ENABLE_PLAN_SCHEDULE_FLAG       || chr(9) ||
          cur2.ENABLE_SHIP_SCHEDULE_FLAG       || chr(9) ||
          cur2.PLAN_SCHEDULE_TYPE              || chr(9) ||
          cur2.SHIP_SCHEDULE_TYPE              || chr(9) ||
          cur2.PLAN_BUCKET_PATTERN_ID          || chr(9) ||
          cur2.SHIP_BUCKET_PATTERN_ID          || chr(9) ||
          cur2.ENABLE_AUTOSCHEDULE_FLAG        || chr(9) ||
          cur2.SCHEDULER_ID                    || chr(9) ||
          cur2.ENABLE_AUTHORIZATIONS_FLAG      || chr(9) ||
          cur2.VENDOR_ID                       || chr(9) ||
          cur2.VENDOR_SITE_ID                  || chr(9) ||
          cur2.ITEM_ID                         || chr(9) ||
          cur2.CATEGORY_ID                     || chr(9) ||
          cur2.ATTRIBUTE_CATEGORY              || chr(9) ||
          cur2.ATTRIBUTE1                      || chr(9) ||
          cur2.ATTRIBUTE2                      || chr(9) ||
          cur2.ATTRIBUTE3                      || chr(9) ||
          cur2.ATTRIBUTE4                      || chr(9) ||
          cur2.ATTRIBUTE5                      || chr(9) ||
          cur2.ATTRIBUTE6                      || chr(9) ||
          cur2.ATTRIBUTE7                      || chr(9) ||
          cur2.ATTRIBUTE8                      || chr(9) ||
          cur2.ATTRIBUTE9                      || chr(9) ||
          cur2.ATTRIBUTE10                     || chr(9) ||
          cur2.ATTRIBUTE11                     || chr(9) ||
          cur2.ATTRIBUTE12                     || chr(9) ||
          cur2.ATTRIBUTE13                     || chr(9) ||
          cur2.ATTRIBUTE14                     || chr(9) ||
          cur2.ATTRIBUTE15                     || chr(9) ||
          cur2.LAST_UPDATE_LOGIN               || chr(9) ||
          cur2.REQUEST_ID                      || chr(9) ||
          cur2.PROGRAM_APPLICATION_ID          || chr(9) ||
          cur2.PROGRAM_ID                      || chr(9) ||
          cur2.PROGRAM_UPDATE_DATE             || chr(9) ||
          cur2.PRICE_UPDATE_TOLERANCE          || chr(9) ||
          cur2.PROCESSING_LEAD_TIME            || chr(9) ||
          cur2.MIN_ORDER_QTY                   || chr(9) ||
          cur2.FIXED_LOT_MULTIPLE              || chr(9) ||
          cur2.DELIVERY_CALENDAR               || chr(9) ||
          cur2.COUNTRY_OF_ORIGIN_CODE          || chr(9) ||
          cur2.ENABLE_VMI_FLAG                 || chr(9) ||
          cur2.VMI_MIN_QTY                     || chr(9) ||
          cur2.VMI_MAX_QTY                     || chr(9) ||
          cur2.ENABLE_VMI_AUTO_REPLENISH_FLAG  || chr(9) ||
          cur2.VMI_REPLENISHMENT_APPROVAL      || chr(9) ||
          cur2.CONSIGNED_FROM_SUPPLIER_FLAG    || chr(9) ||
          cur2.LAST_BILLING_DATE               || chr(9) ||
          cur2.CONSIGNED_BILLING_CYCLE
		  ); 
		  
		  l_processed_count_2 := l_processed_count_2 + 1;
  	  end loop;
  	  
  	  l_processed_count_1 := l_processed_count_1 + 1;
   end loop;
   
   l_err_msg := '';
   if l_processed_count_1 = 0 then   
     l_rejected_count_1 := l_rejected_count_1 + 1;
     l_err_msg := l_ngo_sob || ': Approved Supplier List not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPPOEXT_APPROVED_SUPPLIER_LIST', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );
   
   commit;
   
   l_err_msg := '';
   if l_processed_count_2 = 0 then   
     l_rejected_count_2 := l_rejected_count_2 + 1;
     l_err_msg := l_ngo_sob || ': Approved Supplier List attribute not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPPOEXT_APPROVED_SUPPLIER_LIST_ATTRIBUTE', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );
   
   commit;

  utl_file.fclose(l_utl_file_1);
  utl_file.fclose(l_utl_file_2);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
